package com.flower.dao;

import com.flower.pojo.Flower;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface FlowerRepository extends JpaRepository<Flower, Long> {
    
    // 关键字搜索（名称、类别或存放位置）
    @Query("SELECT f FROM Flower f WHERE " +
           "LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    List<Flower> findByKeyword(@Param("keyword") String keyword);
    
    // 按类别筛选
    List<Flower> findByCategory(String category);
    
    // 按价格范围筛选
    List<Flower> findByPriceBetween(BigDecimal minPrice, BigDecimal maxPrice);
    
    // 按价格大于等于筛选
    List<Flower> findByPriceGreaterThanEqual(BigDecimal minPrice);
    
    // 按库存状态筛选（低库存）
    List<Flower> findByStockQuantityLessThan(Integer threshold);
    
    // 按库存状态筛选（充足库存）
    List<Flower> findByStockQuantityGreaterThanEqual(Integer threshold);
    
    // 组合查询：关键字 + 类别
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category")
    List<Flower> findByKeywordAndCategory(@Param("keyword") String keyword, @Param("category") String category);
    
    // 组合查询：关键字 + 价格范围
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price BETWEEN :minPrice AND :maxPrice")
    List<Flower> findByKeywordAndPriceBetween(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice);
    
    // 组合查询：关键字 + 价格大于等于
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price >= :minPrice")
    List<Flower> findByKeywordAndPriceGreaterThanEqual(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice);
    
    // 组合查询：关键字 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndLowStock(
            @Param("keyword") String keyword, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：类别 + 价格范围
    @Query("SELECT f FROM Flower f WHERE f.category = :category AND f.price BETWEEN :minPrice AND :maxPrice")
    List<Flower> findByCategoryAndPriceBetween(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice);
    
    // 组合查询：类别 + 价格大于等于
    @Query("SELECT f FROM Flower f WHERE f.category = :category AND f.price >= :minPrice")
    List<Flower> findByCategoryAndPriceGreaterThanEqual(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice);
    
    // 组合查询：类别 + 库存状态
    @Query("SELECT f FROM Flower f WHERE f.category = :category AND f.stockQuantity < :threshold")
    List<Flower> findByCategoryAndLowStock(
            @Param("category") String category, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE f.category = :category AND f.stockQuantity >= :threshold")
    List<Flower> findByCategoryAndNormalStock(
            @Param("category") String category, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：价格范围 + 库存状态
    @Query("SELECT f FROM Flower f WHERE f.price BETWEEN :minPrice AND :maxPrice AND f.stockQuantity < :threshold")
    List<Flower> findByPriceBetweenAndLowStock(
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE f.price BETWEEN :minPrice AND :maxPrice AND f.stockQuantity >= :threshold")
    List<Flower> findByPriceBetweenAndNormalStock(
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：价格大于等于 + 库存状态
    @Query("SELECT f FROM Flower f WHERE f.price >= :minPrice AND f.stockQuantity < :threshold")
    List<Flower> findByPriceGreaterThanEqualAndLowStock(
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE f.price >= :minPrice AND f.stockQuantity >= :threshold")
    List<Flower> findByPriceGreaterThanEqualAndNormalStock(
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：关键字 + 类别 + 价格范围
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price BETWEEN :minPrice AND :maxPrice")
    List<Flower> findByKeywordAndCategoryAndPriceBetween(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice);
    
    // 组合查询：关键字 + 类别 + 价格大于等于
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price >= :minPrice")
    List<Flower> findByKeywordAndCategoryAndPriceGreaterThanEqual(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice);
    
    // 组合查询：关键字 + 类别 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndCategoryAndLowStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndCategoryAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：关键字 + 价格范围 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndPriceBetweenAndLowStock(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndPriceBetweenAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：关键字 + 价格大于等于 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndPriceGreaterThanEqualAndLowStock(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndPriceGreaterThanEqualAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：类别 + 价格范围 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "f.category = :category " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByCategoryAndPriceBetweenAndLowStock(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "f.category = :category " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByCategoryAndPriceBetweenAndNormalStock(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：类别 + 价格大于等于 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "f.category = :category " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByCategoryAndPriceGreaterThanEqualAndLowStock(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "f.category = :category " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByCategoryAndPriceGreaterThanEqualAndNormalStock(
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：关键字 + 类别 + 价格范围 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndCategoryAndPriceBetweenAndLowStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price BETWEEN :minPrice AND :maxPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndCategoryAndPriceBetweenAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("maxPrice") BigDecimal maxPrice, 
            @Param("threshold") Integer threshold);
    
    // 组合查询：关键字 + 类别 + 价格大于等于 + 库存状态
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity < :threshold")
    List<Flower> findByKeywordAndCategoryAndPriceGreaterThanEqualAndLowStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
    
    @Query("SELECT f FROM Flower f WHERE " +
           "(LOWER(f.name) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.category) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.storageLocation) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND f.category = :category " +
           "AND f.price >= :minPrice " +
           "AND f.stockQuantity >= :threshold")
    List<Flower> findByKeywordAndCategoryAndPriceGreaterThanEqualAndNormalStock(
            @Param("keyword") String keyword, 
            @Param("category") String category, 
            @Param("minPrice") BigDecimal minPrice, 
            @Param("threshold") Integer threshold);
} 